package com.wangzhe.builder;

import com.wangzhe.bean.Constants;
import com.wangzhe.bean.FieldInfo;
import com.wangzhe.bean.TableInfo;
import com.wangzhe.utils.JsonUtils;
import com.wangzhe.utils.PropertiesUtils;
import com.wangzhe.utils.StringUtils;
import org.apache.commons.lang3.ArrayUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;


import java.sql.*;
import java.util.*;

/**
 * @author 王哲
 * @ClassName BuilderTable
 * @create 2023--六月--上午11:26
 * @Description 读取mysql的表
 * @Version V1.0
 */
public class BuilderTable {

    private static final Logger logger = LoggerFactory.getLogger(BuilderTable.class);
    private static Connection connection = null;

    /*关键SQL定义*/
    private static final String SQL_SHOW_TABLE_STATUS = "SHOW TABLE STATUS";
    private static final String SQL_SHOW_TABLE_FIELDS = "SHOW FULL FIELDS FROM %s";
    private static final String SQL_SHOW_TABLE_INDEX = "SHOW INDEX FROM %s";

    /*拿到配置信息*/
    static {
        String driverName = PropertiesUtils.getString("db.driver.name");
        String url = PropertiesUtils.getString("db.url");
        String username = PropertiesUtils.getString("db.username");
        String password = PropertiesUtils.getString("db.password");

        try {
            Class.forName(driverName);
            connection = DriverManager.getConnection(url, username, password);
        } catch (Exception e) {
            logger.error("数据库链接失败====" + e.getMessage());
        }
    }

    public static List<TableInfo>   getTables() {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        List<TableInfo> tableInfos = new ArrayList<>();

        try {
            preparedStatement = connection.prepareStatement(SQL_SHOW_TABLE_STATUS);

            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                String tableName = resultSet.getString("Name");
                String comment = resultSet.getString("Comment");

                String beanName = tableName;
                if (Constants.IGNORE_TABLE_PREFIX) {
                    beanName = tableName.substring(beanName.indexOf("_") + 1);
                }
                beanName = processField(beanName, true);

                TableInfo tableInfo = new TableInfo();
                tableInfo.setTableName(tableName);
                tableInfo.setBeanName(beanName);
                tableInfo.setComment(comment);
                tableInfo.setBeanParamName(beanName + Constants.DB_SUFFIX_BEAN_PARAM);


                readFieldInfo(tableInfo);

                readIndexInfo(tableInfo);

                tableInfos.add(tableInfo);
            }

        } catch (SQLException e) {
            logger.error("数据库链接失败====" + e.getMessage());
        } finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return tableInfos;
    }

    /**
     * 处理字段
     *
     * @param tableInfo
     * @return
     */
    private static void readFieldInfo(TableInfo tableInfo) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        List<FieldInfo> fieldInfoList = new ArrayList<>();

        //扩展字段
        List<FieldInfo> extendFieldInfoList = new ArrayList<>();

        try {
            preparedStatement = connection.prepareStatement(String.format(SQL_SHOW_TABLE_FIELDS, tableInfo.getTableName()));

            resultSet = preparedStatement.executeQuery();

            boolean haveDateTime = false;
            boolean haveDate = false;
            boolean haveBigDecimal = false;

            while (resultSet.next()) {
                String field = resultSet.getString("Field");
                String type = resultSet.getString("Type");
                String extra = resultSet.getString("Extra");
                String comment = resultSet.getString("Comment");

                //除了类型字段 去除括号
                if (type.contains("(")) {
                    type = type.substring(0, type.indexOf("("));
                }

                //处理字段 field
                String propertyName = processField(field, false);

                FieldInfo fieldInfo = new FieldInfo();
                fieldInfo.setFieldName(field);
                fieldInfo.setComment(comment);
                fieldInfo.setSqlType(type);
                fieldInfo.setFieldJavaType(processJavaType(type));
                fieldInfo.setAutoIncrement("auto_increment".equals(extra) ? true : false);
                fieldInfo.setPropertyName(propertyName);

                fieldInfoList.add(fieldInfo);

                if (ArrayUtils.contains(Constants.SQL_DATE_TIME_TYPES, type)){
                    haveDateTime = true;
                }
                if (ArrayUtils.contains(Constants.SQL_DATE_TYPES, type)){
                    haveDate = true;
                }
                if (ArrayUtils.contains(Constants.SQL_DOUBLE_TYPES, type)){
                    haveBigDecimal = true;
                }

                if (ArrayUtils.contains(Constants.SQL_STRING_TYPES, fieldInfo.getSqlType())) {
                    FieldInfo fieldInfo1 = new FieldInfo();
                    fieldInfo1.setFieldName(fieldInfo.getFieldName());
                    fieldInfo1.setPropertyName(fieldInfo.getPropertyName() + Constants.DB_SUFFIX_PARAM_LIKE);
                    fieldInfo1.setFieldJavaType(fieldInfo.getFieldJavaType());

                    extendFieldInfoList.add(fieldInfo1);
                }

                //如果是时间类型
                if (ArrayUtils.contains(Constants.SQL_DATE_TYPES, fieldInfo.getSqlType()) ||
                        ArrayUtils.contains(Constants.SQL_DATE_TIME_TYPES, fieldInfo.getSqlType())) {

                    FieldInfo fieldInfo1 = new FieldInfo();
                    fieldInfo1.setFieldName(fieldInfo.getFieldName());
                    fieldInfo1.setPropertyName(fieldInfo.getPropertyName() + Constants.DB_SUFFIX_PARAM_START);
                    fieldInfo1.setFieldJavaType("String");
                    fieldInfo1.setSqlType("Date");
                    extendFieldInfoList.add(fieldInfo1);

                    fieldInfo1 = new FieldInfo();
                    fieldInfo1.setFieldName(fieldInfo.getFieldName());
                    fieldInfo1.setPropertyName(fieldInfo.getPropertyName() + Constants.DB_SUFFIX_PARAM_END);
                    fieldInfo1.setFieldJavaType("String");
                    fieldInfo1.setSqlType("Date");
                    extendFieldInfoList.add(fieldInfo1);
                }
            }
            tableInfo.setHaveDateTime(haveDateTime);
            tableInfo.setHaveDate(haveDate);
            tableInfo.setHaveBigDecimal(haveBigDecimal);
            tableInfo.setFieldInfo(fieldInfoList);
            tableInfo.setExtendFieldInfo(extendFieldInfoList);
        } catch (SQLException e) {
            logger.error("数据库链接失败====" + e.getMessage());
        } finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 处理索引
     *
     * @param tableInfo
     * @return
     */
    private static void readIndexInfo(TableInfo tableInfo) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        List<FieldInfo> fieldInfoList = new ArrayList<>();

        Map<String,FieldInfo> temMap = new HashMap<>();
        for (FieldInfo fieldInfo : tableInfo.getFieldInfo()) {
            temMap.put(fieldInfo.getFieldName(),fieldInfo);
        }

        try {
            preparedStatement = connection.prepareStatement(String.format(SQL_SHOW_TABLE_INDEX, tableInfo.getTableName()));

            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                String keyName = resultSet.getString("Key_name");
                Integer nonUnique = resultSet.getInt("Non_unique");
                String columnName = resultSet.getString("Column_name");

                if (nonUnique == 1) {
                    continue;
                }
                List<FieldInfo> fieldInfos = tableInfo.getKeyIndexMap().get(keyName);
                if (fieldInfos == null) {
                    fieldInfos = new ArrayList<>();
                    tableInfo.getKeyIndexMap().put(keyName, fieldInfos);
                }
                fieldInfos.add(temMap.get(columnName));
            }

        } catch (SQLException e) {
            logger.error("读取索引失败====" + e.getMessage());
        } finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 处理表名
     *
     * @param field
     * @param upperCaseFirst
     * @return
     */
    private static String processField(String field, boolean upperCaseFirst) {
        StringBuffer stringBuffer = new StringBuffer();
        String[] str = field.split("_");
        stringBuffer.append(upperCaseFirst ? StringUtils.upperCaseFirst(str[0]) : str[0]);
        for (int i = 1; i < str.length; i++) {
            stringBuffer.append(StringUtils.upperCaseFirst(str[i]));
        }
        return stringBuffer.toString();
    }

    private static String processJavaType(String type) {
        if (ArrayUtils.contains(Constants.SQL_STRING_TYPES, type)) {
            return "String";
        } else if (ArrayUtils.contains(Constants.SQL_INTEGER_TYPES, type)) {
            return "Integer";
        } else if (ArrayUtils.contains(Constants.SQL_LONG_TYPES, type)) {
            return "Long";
        } else if (ArrayUtils.contains(Constants.SQL_DOUBLE_TYPES, type)) {
            return "Double";
        } else if (ArrayUtils.contains(Constants.SQL_DATE_TYPES, type)||ArrayUtils.contains(Constants.SQL_DATE_TIME_TYPES, type)) {
            return "Date";
        }else {
            throw new RuntimeException("未知类型"+type);
        }
    }

}
